package com.qfjg.mappers;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.qfjg.entity.ProjectsInfo;
import com.qfjg.myutil.JsonbTypeHandler;
import org.apache.ibatis.annotations.*;

import java.util.ArrayList;
import java.util.List;

@Mapper
public interface ProjectsInfoMapper extends BaseMapper<ProjectsInfo> {


    @Select("SELECT * FROM projects_info " +
            "WHERE uid = #{uid} " +
            "ORDER BY " +
            "CASE WHEN status = '待提交' THEN 1 " +
            "WHEN status = '已提交' THEN 2 " +
            "ELSE 3 END ASC, " +
            "create_time DESC")
    List<ProjectsInfo> getProjectsByUid(@Param("uid") String uid);


    @Select("""
                SELECT * FROM (
                    SELECT * 
                    FROM projects_info 
                    WHERE uid = #{uid}
                    UNION 
                    SELECT pi.* 
                    FROM projects_info pi 
                    JOIN phone_list pl 
                    ON pi.project_id = pl.project_id 
                    WHERE pl.phone_number = #{phoneNumber}
                ) AS combined_results
                ORDER BY CASE 
                    WHEN status = '待提交' THEN 1 
                    WHEN status = '已提交' THEN 2 
                    ELSE 3 
                END ASC, create_time DESC
            """)
    @Results({
            @Result(property = "uid", column = "uid"),
            @Result(property = "projectId", column = "project_id"),
            @Result(property = "shipList", column = "ship_list", typeHandler = JsonbTypeHandler.class),
            @Result(property = "wasteTypes", column = "waste_types", typeHandler = JsonbTypeHandler.class)
    })
    List<ProjectsInfo> getProjectsByUidAndPhone(@Param("uid") String uid, @Param("phoneNumber") String phone);


    @Select({
            "<script>",
            "SELECT * FROM projects_info",
            "WHERE status = '已提交'",
            "<if test='projectsInfo.certificateNumber != null and projectsInfo.certificateNumber != \"\"'>",
            "  AND certificate_number LIKE CONCAT('%', #{projectsInfo.certificateNumber}, '%')",
            "</if>",
            "<if test='projectsInfo.applierOrg != null and projectsInfo.applierOrg != \"\"'>",
            "  AND applier_org LIKE CONCAT('%', #{projectsInfo.applierOrg}, '%')",
            "</if>",
            "<if test='projectsInfo.workUnit != null and projectsInfo.workUnit != \"\"'>",
            "  AND work_unit LIKE CONCAT('%', #{projectsInfo.workUnit}, '%')",
            "</if>",
            "<if test='projectsInfo.proName != null and projectsInfo.proName != \"\"'>",
            "  AND pro_name LIKE CONCAT('%', #{projectsInfo.proName}, '%')",
            "</if>",
            "<if test='projectsInfo.contacts != null and projectsInfo.contacts != \"\"'>",
            "  AND contacts LIKE CONCAT('%', #{projectsInfo.contacts}, '%')",
            "</if>",
            "<if test='projectsInfo.phoneNumber != null and projectsInfo.phoneNumber != \"\"'>",
            "  AND phone_number LIKE CONCAT('%', #{projectsInfo.phoneNumber}, '%')",
            "</if>",
            "<if test='projectsInfo.dumpingAreaName != null and projectsInfo.dumpingAreaName != \"\"'>",
            "  AND dumping_area_name LIKE CONCAT('%', #{projectsInfo.dumpingAreaName}, '%')",
            "</if>",
            "<if test='projectsInfo.comptDeptId != null and projectsInfo.comptDeptId != \"\"'>",
            "  AND compt_dept_id = #{projectsInfo.comptDeptId}",
            "</if>",
            "<if test='projectsInfo.comptDeptId == null or projectsInfo.comptDeptId == \"\"'>",
            "  AND compt_dept_id IN",
            "  <foreach item='item' index='index' collection='rids' open='(' separator=',' close=')'>",
            "    #{item}",
            "  </foreach>",
            "</if>",
            "<if test='projectsInfo.dumpingMethod != null and projectsInfo.dumpingMethod != \"\"'>",
            "  AND dumping_method = #{projectsInfo.dumpingMethod}",
            "</if>",
            "<if test='!projectsInfo.shipList.isEmpty() and projectsInfo.shipList[0] != null and projectsInfo.shipList[0] != \"\"'>",
            "   AND EXISTS (SELECT 1 FROM jsonb_array_elements_text(ship_list::jsonb) AS elem WHERE elem = #{projectsInfo.shipList[0]})",
            "</if>",
            "<if test='!projectsInfo.wasteTypes.isEmpty() and projectsInfo.wasteTypes[0] != null and projectsInfo.wasteTypes[0] != \"\"'>",
            "   AND EXISTS (SELECT 1 FROM jsonb_array_elements_text(waste_types::jsonb) AS elem WHERE elem = #{projectsInfo.wasteTypes[0]})",
            "</if>",
            "ORDER BY create_time DESC",
            "</script>"
    })
    @Results({
            @Result(property = "uid", column = "uid"),
            @Result(property = "projectId", column = "project_id"),
            @Result(property = "shipList", column = "ship_list", typeHandler = JsonbTypeHandler.class),
            @Result(property = "wasteTypes", column = "waste_types", typeHandler = JsonbTypeHandler.class)
    })
    List<ProjectsInfo> getProjectsByAdminFilter(@Param("projectsInfo") ProjectsInfo projectsInfo,
                                                @Param("rids") ArrayList<String> rids);



}
